package mac5855.app.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mac5855.app.models.Pergunta;
import mac5855.app.models.Resposta;
import mac5855.app.models.Usuario;

public class JdbcRespostaDAO implements RespostaDAO {

	@Override
	public void create(Resposta resposta) {
		Connection conn = null;
		conn = JdbcDAOFactory.createConnection();
		String sql = "insert into respostas (titulo, data, usuario_id, pergunta_id) " + "values (?, ?, ?, ?)";
		PreparedStatement stm;
		try {
			stm = conn.prepareStatement(sql);
			stm.setString(1, resposta.getTitulo());
			stm.setDate(2, new java.sql.Date(resposta.getData().getTime()));
			stm.setString(3, resposta.getUsuario().getEmail());
			stm.setInt(4, resposta.getPergunta().getId());
			stm.execute();

			stm.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("Gravado!");
		JdbcDAOFactory.releaseConnection(conn);
	}

	@Override
	public void delete(Integer id) {
		Connection conn = null;
		conn = JdbcDAOFactory.createConnection();
		try {
			PreparedStatement stmt = conn.prepareStatement("delete from respostas where id = ?");

			stmt.setInt(1, id);
			stmt.execute();

			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("Gravado!");
		JdbcDAOFactory.releaseConnection(conn);

	}

	@Override
	public Resposta find(Resposta resposta) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Resposta[] findAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void update(Resposta transientObject) {
		// TODO Auto-generated method stub

	}

	@Override
	public Resposta findById(Integer id) {
		Connection conn = JdbcDAOFactory.createConnection();
		Resposta resposta = null;
		try {
			PreparedStatement stmt = conn.prepareStatement("select * from respostas where id = ?");
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			if (rs.next()) { // Query only returns 1 record in the result set;
				// only 1
				resposta = new Resposta();
				resposta.setId(rs.getInt("id"));
				resposta.setTitulo(rs.getString("titulo"));
				resposta.setData(rs.getDate("data"));
				resposta.setVotoNegativo(rs.getInt("voto_negativo"));
				resposta.setVotoPositivo(rs.getInt("voto_positivo"));

				PerguntaDAO perguntaDAO = DAOFactory.getDAOFactory(1).getPerguntaDAO();
				Pergunta pergunta = perguntaDAO.findById(rs.getInt("pergunta_id"));
				resposta.setPergunta(pergunta);

				UsuarioDAO usuarioDAO = DAOFactory.getDAOFactory(1).getUsuarioDAO();
				Usuario usuario = usuarioDAO.findByEmail(rs.getString("usuario_id"));
				resposta.setUsuario(usuario);

			}
			rs.close();
			stmt.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		JdbcDAOFactory.releaseConnection(conn);

		return resposta;
	}

	@Override
	public Resposta[] findByPergunta(Pergunta pergunta) {
		Connection conn = JdbcDAOFactory.createConnection();
		List<Resposta> respostas = new ArrayList<Resposta>();
		try {
			PreparedStatement stmt = conn.prepareStatement("select * from respostas where pergunta_id = ?");
			stmt.setInt(1, pergunta.getId());
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				// criando o objeto Contato
				Resposta resposta = new Resposta();
				resposta.setId(rs.getInt("id"));
				resposta.setTitulo(rs.getString("titulo"));
				resposta.setData(rs.getDate("data"));
				resposta.setVotoNegativo(rs.getInt("voto_negativo"));
				resposta.setVotoPositivo(rs.getInt("voto_positivo"));

				UsuarioDAO usuarioDAO = DAOFactory.getDAOFactory(1).getUsuarioDAO();
				Usuario usuario = usuarioDAO.findByEmail(rs.getString("usuario_id"));
				resposta.setUsuario(usuario);

				resposta.setPergunta(pergunta);
				// adicionando o objeto à lista
				respostas.add(resposta);
			}

			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		JdbcDAOFactory.releaseConnection(conn);

		return respostas.toArray(new Resposta[respostas.size()]);
	}

	@Override
	public void vote(int id, boolean voteThrough) {

		Connection conn = JdbcDAOFactory.createConnection();

		String column = (voteThrough) ? "voto_positivo" : "voto_negativo";

		try {
			PreparedStatement stmt = conn.prepareStatement("select " + column + " from respostas where id = ?");
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				int pontos = rs.getInt(1);
				rs.close();
				stmt.close();
				stmt = conn.prepareStatement("update respostas set " + column + " = ? where id = ?");
				stmt.setInt(1, pontos + 1);
				stmt.setInt(2, id);
				stmt.executeUpdate();
				stmt.close();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		JdbcDAOFactory.releaseConnection(conn);

	}

}
